import sqlite3
from flask import Flask, render_template, request, redirect, url_for, flash, session, jsonify, Response, send_file
import os
import datetime
import traceback
from db_operations import DBOperations
import csv
import io
from werkzeug.utils import secure_filename
import pandas as pd
import cv2
from pylibdmtx.pylibdmtx import decode
from pyzbar.pyzbar import decode as decode_barcode
import logging
from functools import wraps
import numpy as np
from werkzeug.security import generate_password_hash, check_password_hash
import re

# 配置日志
logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('app.log', mode='a', encoding='utf-8'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)
logger.info('日志系统已初始化')

def init_db():
    """初始化数据库"""
    # 创建上传目录
    os.makedirs(os.path.join('static', 'uploads'), exist_ok=True)
    
    conn = sqlite3.connect('equipment.db', check_same_thread=False)
    c = conn.cursor()
    
    # 创建设备表
    c.execute('''CREATE TABLE IF NOT EXISTS equipment
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  date TEXT,
                  sn TEXT UNIQUE,
                  model TEXT,
                  manufacturer TEXT,
                  network_element TEXT,
                  type TEXT,
                  status TEXT,
                  standard TEXT,
                  photo_path TEXT,
                  outbound_time TEXT,
                  discard_time TEXT,
                  original_network_element TEXT,
                  current_network_element TEXT,
                  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
    
    # 添加新字段（如果不存在）
    try:
        c.execute("ALTER TABLE equipment ADD COLUMN outbound_time TEXT")
    except sqlite3.OperationalError:
        pass  # 列已存在
        
    try:
        c.execute("ALTER TABLE equipment ADD COLUMN discard_time TEXT")
    except sqlite3.OperationalError:
        pass  # 列已存在
        
    try:
        c.execute("ALTER TABLE equipment ADD COLUMN original_network_element TEXT")
    except sqlite3.OperationalError:
        pass  # 列已存在
        
    try:
        c.execute("ALTER TABLE equipment ADD COLUMN current_network_element TEXT")
    except sqlite3.OperationalError:
        pass  # 列已存在
    
    # 创建用户表
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  username TEXT UNIQUE,
                  password TEXT)''')
    
    # 创建操作日志表
    c.execute('''CREATE TABLE IF NOT EXISTS operation_logs
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  operation_type TEXT,
                  description TEXT,
                  operation_time TEXT,
                  username TEXT)''')
    
    # 插入默认用户
    try:
        c.execute("INSERT INTO users (username, password) VALUES (?, ?)", 
                 ('yangyang', 'yangyang123'))
    except sqlite3.IntegrityError:
        pass
    
    conn.commit()
    conn.close()

# 创建Flask应用
app = Flask(__name__)
app.secret_key = 'dev'

# 确保数据库已初始化
init_db()

# 主路由
@app.route('/')
def index():
    return redirect(url_for('login'))

@app.route('/dashboard')
def dashboard():
    if 'username' not in session:
        return redirect(url_for('login'))
    
    # 连接数据库获取统计数据
    conn = sqlite3.connect('equipment.db')
    c = conn.cursor()
    
    # 获取设备状态统计
    c.execute("""
        SELECT 
            SUM(CASE WHEN status = '在库' THEN 1 ELSE 0 END) as in_stock,
            SUM(CASE WHEN status = '出库' THEN 1 ELSE 0 END) as outbound,
            SUM(CASE WHEN status = '销库' THEN 1 ELSE 0 END) as discarded,
            COUNT(*) as total
        FROM equipment
    """)
    stats = c.fetchone()
    
    status_stats = {
        'in_stock': stats[0] or 0,
        'outbound': stats[1] or 0,
        'discarded': stats[2] or 0,
        'total': stats[3] or 0
    }
    
    # 获取最近操作记录
    c.execute("""
        SELECT operation_type, description, operation_time 
        FROM operation_logs 
        ORDER BY operation_time DESC 
        LIMIT 10
    """)
    recent_operations = c.fetchall()
    
    conn.close()
    
    return render_template('dashboard.html',
                         status_stats=status_stats,
                         recent_operations=recent_operations)

# 登录装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'username' not in session:
            flash('请先登录', 'warning')
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# 记录操作日志
def log_operation(operation_type, details):
    try:
        conn = sqlite3.connect('equipment.db')
        c = conn.cursor()
        
        # 检查表结构并添加缺失的user_id列
        c.execute("PRAGMA table_info(operation_logs)")
        columns = [column[1] for column in c.fetchall()]
        if 'user_id' not in columns:
            c.execute("ALTER TABLE operation_logs ADD COLUMN user_id TEXT")
            
        c.execute('INSERT INTO operation_logs (operation_type, description, operation_time, username, user_id) VALUES (?, ?, ?, ?, ?)',
                 (operation_type, details, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), session.get('username'), session.get('user_id')))
        conn.commit()
    except Exception as e:
        logger.error(f"记录操作日志失败: {str(e)}")
    finally:
        conn.close()

# 登录路由
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        if not username or not password:
            flash('用户名和密码不能为空', 'danger')
            return render_template('login.html')
        
        try:
            conn = sqlite3.connect('equipment.db')
            c = conn.cursor()
            c.execute('SELECT id, password FROM users WHERE username = ?', (username,))
            user = c.fetchone()
            
            if user and user[1] == password:  # 实际应用中应该使用密码哈希
                session['user_id'] = user[0]
                session['username'] = username
                log_operation('login', f'用户 {username} 登录成功')
                logger.info(f'用户 {username} 登录成功')
                flash('登录成功', 'success')
                return redirect(url_for('dashboard'))
            else:
                logger.warning(f'登录失败: 用户名或密码错误 - {username}')
                flash('用户名或密码错误', 'danger')
        except Exception as e:
            logger.error(f"登录失败: {str(e)}")
            logger.error(f"完整错误信息: {traceback.format_exc()}")
            flash('登录失败，请稍后重试', 'danger')
        finally:
            conn.close()
    
    return render_template('login.html')

# 登出路由
@app.route('/logout')
def logout():
    if 'username' in session:
        log_operation('logout', f'用户 {session["username"]} 登出')
    session.clear()
    flash('已成功登出', 'success')
    return redirect(url_for('login'))

# 登录路由
@app.route('/inbound', methods=['GET', 'POST'])
def inbound():
    if 'username' not in session:
        return redirect(url_for('login'))
    
    if request.method == 'POST':
        try:
            logger.info(f'开始处理设备入库请求，用户: {session.get("username")}')
            # 获取表单数据
            date = request.form['date']
            sn = request.form['sn']
            model = request.form['model']
            manufacturer = request.form['manufacturer']
            network_element = request.form.get('network_element', '')
            device_type = request.form.get('type', '')
            standard = request.form.get('standard', '')
            
            logger.info(f'入库设备信息 - SN: {sn}, 型号: {model}, 制造商: {manufacturer}')
            
            # 处理照片上传
            photo_path = ''
            if 'equipment_image' in request.files:
                file = request.files['equipment_image']
                if file.filename:
                    try:
                        # 获取文件扩展名
                        file_ext = os.path.splitext(file.filename)[1]
                        # 使用SN码作为文件名
                        filename = f"{sn}{file_ext}"
                        photo_path = os.path.join('uploads', filename)
                        logger.info(f'上传设备照片: {filename}')
                        file.save(os.path.join('static', photo_path))
                    except Exception as e:
                        logger.error(f'照片上传失败: {str(e)}')
                        logger.error(f'完整错误信息: {traceback.format_exc()}')
                        flash('照片上传失败，请重试', 'danger')
            
            # 使用上下文管理器处理数据库连接
            with sqlite3.connect('equipment.db') as conn:
                c = conn.cursor()
                
                # 开始事务
                c.execute("BEGIN TRANSACTION")
                
                try:
                    # 插入设备记录
                    c.execute("""INSERT INTO equipment 
                                (date, sn, model, manufacturer, network_element, type, status, standard, photo_path)
                                VALUES (?, ?, ?, ?, ?, ?, '在库', ?, ?)""",
                             (date, sn, model, manufacturer, network_element, device_type, standard, photo_path))
                    
                    # 验证是否插入成功
                    if c.rowcount == 0:
                        raise Exception('设备入库失败')
                    
                    # 记录操作日志
                    c.execute("""INSERT INTO operation_logs 
                                (operation_type, description, operation_time, username)
                                VALUES (?, ?, ?, ?)""",
                             ('inbound', f'入库设备 SN: {sn}', datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 
                              session['username']))
                    
                    # 验证日志记录是否成功
                    if c.rowcount == 0:
                        raise Exception('操作日志记录失败')
                    
                    # 立即验证设备是否已入库
                    c.execute("SELECT * FROM equipment WHERE sn=?", (sn,))
                    equipment = c.fetchone()
                    if not equipment:
                        raise Exception('设备入库验证失败')
                    
                    # 提交事务
                    conn.commit()
                    
                    flash('设备入库成功', 'success')
                    return redirect(url_for('dashboard'))
                    
                except sqlite3.IntegrityError as e:
                    conn.rollback()
                    logger.error(f'数据库完整性错误: {str(e)}')
                    return render_template('inbound.html', error='SN码已存在')
                except Exception as e:
                    conn.rollback()
                    logger.error(f'设备入库失败: {str(e)}')
                    logger.error(f'完整错误信息: {traceback.format_exc()}')
                    return render_template('inbound.html', error=str(e))
        
        except Exception as e:
            logger.error(f'处理入库请求失败: {str(e)}')
            logger.error(f'完整错误信息: {traceback.format_exc()}')
            return render_template('inbound.html', error='处理入库请求失败')
    
    return render_template('inbound.html')

def process_image(image_path):
    """处理图片，识别条形码或Data Matrix码"""
    try:
        # 读取图片
        image = cv2.imread(image_path)
        if image is None:
            return None, "无法读取图片"

        # 尝试识别条形码
        barcodes = decode_barcode(image)
        if barcodes:
            for barcode in barcodes:
                barcode_data = barcode.data.decode('utf-8')
                # 检查是否是EAN-13格式
                if re.match(r'^\d{13}$', barcode_data):
                    return barcode_data, "条形码"

        # 尝试识别Data Matrix码
        dmtx_codes = decode(image)
        if dmtx_codes:
            for code in dmtx_codes:
                return code.data.decode('utf-8'), "Data Matrix码"

        return None, "未识别到有效的条形码或Data Matrix码"
    except Exception as e:
        return None, f"识别过程出错: {str(e)}"

@app.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify({'error': '没有文件'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': '没有选择文件'}), 400

    if file:
        # 保存上传的文件
        filename = secure_filename(file.filename)
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(filepath)

        # 处理图片
        sn_code, code_type = process_image(filepath)
        
        # 删除临时文件
        try:
            os.remove(filepath)
        except:
            pass

        if sn_code:
            return jsonify({
                'success': True,
                'sn_code': sn_code,
                'code_type': code_type
            })
        else:
            return jsonify({'error': '无法识别条形码或Data Matrix码'}), 400

@app.route('/recognize_sn', methods=['POST'])
@login_required
def recognize_sn():
    """识别SN码图片中的Data Matrix码"""
    def make_response(success, message, error=None, error_code=200):
        response = {
            'success': success,
            'message': message
        }
        if error:
            response['error'] = error
        logger.info(f"Response: {response}")  # 添加日志记录
        return jsonify(response), error_code if not success else 200

    try:
        logger.info("开始处理SN码识别请求")
        # 检查是否上传了文件
        if 'sn_image' not in request.files:
            logger.warning("未找到上传的文件")
            return make_response(False, '请上传SN码图片', 'missing_file', 400)
            
        file = request.files['sn_image']
        if not file or not file.filename:
            logger.warning("文件无效")
            return make_response(False, '无效的图片文件', 'invalid_file', 400)
            
        # 检查文件类型
        if not file.filename.lower().endswith(('.png', '.jpg', '.jpeg', '.bmp')):
            logger.warning(f"不支持的文件格式: {file.filename}")
            return make_response(False, '只支持PNG、JPG、JPEG、BMP格式的图片', 'invalid_file_format', 400)
            
        # 创建临时文件
        import tempfile
        import os
        temp_path = os.path.join(tempfile.gettempdir(), secure_filename(file.filename))
        file.save(temp_path)
        logger.info(f"文件已保存到临时路径: {temp_path}")
        
        try:
            # 使用与本地脚本相同的识别方法
            from pylibdmtx import pylibdmtx
            from PIL import Image
            
            # 读取图片
            image = Image.open(temp_path)
            
            # 尝试解码
            decoded_objects = pylibdmtx.decode(image)
            logger.info(f"解码结果: {decoded_objects}")
            
            if not decoded_objects:
                logger.warning("未检测到Data Matrix码")
                return make_response(False, '未检测到Data Matrix码，请确保图片清晰且包含完整的Data Matrix码', 'no_dmtx_found', 404)
                
            # 获取SN码
            sn = decoded_objects[0].data.decode('utf-8')
            logger.info(f"识别到的SN码: {sn}")
            
            # 验证SN码格式（根据实际需求调整）
            if not sn or len(sn) < 8:  # 假设SN码至少8位
                logger.warning(f"SN码格式不正确: {sn}")
                return make_response(False, '识别到的SN码格式不正确', 'invalid_sn_format', 400)
                
            # 记录操作日志
            log_operation('recognize_sn', f'识别SN码: {sn}')
            
            # 确保SN码格式正确并去除可能的空白字符
            sn = sn.strip()
            logger.info(f"最终SN码: {sn}")
            return make_response(True, sn)
            
        finally:
            # 清理临时文件
            if os.path.exists(temp_path):
                try:
                    os.unlink(temp_path)
                    logger.info("临时文件已删除")
                except Exception as e:
                    logger.error(f"删除临时文件失败: {str(e)}")
                
    except ImportError as e:
        logger.error(f"导入依赖库失败: {str(e)}")
        return make_response(False, '系统缺少必要的依赖库，请联系管理员', 'missing_dependencies', 500)
    except Exception as e:
        logger.error(f"SN码识别失败: {str(e)}")
        return make_response(False, f'识别失败: {str(e)}', 'recognition_error', 500)

@app.route('/match_network_element', methods=['POST'])
@login_required
def match_network_element():
    """匹配网元名称和设备类型"""
    try:
        # 获取SN码和CSV文件
        sn = request.form.get('sn')
        csv_file = request.files.get('csv_file')
        
        if not sn or not csv_file:
            return jsonify({
                'success': False,
                'message': '缺少必要参数',
                'error': 'missing_parameters'
            }), 400
            
        # 检查文件类型
        if not csv_file.filename.lower().endswith('.csv'):
            return jsonify({
                'success': False,
                'message': '只支持CSV格式的文件',
                'error': 'invalid_file_format'
            }), 400
            
        # 创建临时文件
        import tempfile
        import os
        temp_path = os.path.join(tempfile.gettempdir(), secure_filename(csv_file.filename))
        csv_file.save(temp_path)
        
        try:
            # 尝试多种编码格式读取CSV
            import pandas as pd
            encodings = ['utf-8', 'gbk', 'gb18030', 'big5']
            df = None
            
            for encoding in encodings:
                try:
                    df = pd.read_csv(temp_path, encoding=encoding)
                    break
                except UnicodeDecodeError:
                    continue
                    
            if df is None:
                return jsonify({
                    'success': False,
                    'message': '无法读取CSV文件，请检查文件编码',
                    'error': 'file_encoding_error'
                }), 400
                
            # 检查必要字段
            required_fields = ['资产序列号', '网元名称', '单板类型', '单板名称']
            missing_fields = [field for field in required_fields if field not in df.columns]
            if missing_fields:
                return jsonify({
                    'success': False,
                    'message': f'CSV文件缺少必要字段: {", ".join(missing_fields)}',
                    'error': 'missing_fields'
                }), 400
                
            # 匹配SN码
            matched_row = df[df['资产序列号'] == sn]
            
            if matched_row.empty:
                return jsonify({
                    'success': False,
                    'message': '未找到匹配的网元信息',
                    'error': 'no_match'
                }), 404
                
            # 获取匹配结果
            network_element = matched_row['网元名称'].values[0]
            device_type = matched_row['单板类型'].values[0]
            board_name = matched_row['单板名称'].values[0]
            
            # 记录操作日志
            log_operation('match_network_element', 
                         f'匹配网元: SN={sn}, 网元={network_element}, 类型={device_type}, 单板名称={board_name}')
            
            return jsonify({
                'success': True,
                'message': '网元匹配成功',
                'network_element': network_element,
                'device_type': device_type,
                'board_name': board_name
            })
            
        finally:
            # 清理临时文件
            if os.path.exists(temp_path):
                os.unlink(temp_path)
                
    except Exception as e:
        logger.error(f"网元匹配失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': f'网元匹配失败: {str(e)}',
            'error': 'matching_error'
        }), 500

@app.route('/delete_equipment', methods=['POST'])
def delete_equipment():
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        sn = request.form.get('sn')
        if not sn:
            return jsonify({
                'success': False,
                'message': '缺少SN码参数',
                'error': 'missing_sn'
            }), 400
            
        if DBOperations().delete_equipment(sn):
            return jsonify({
                'success': True,
                'message': '设备记录已删除'
            })
        else:
            return jsonify({
                'success': False,
                'message': '未找到该SN码的设备记录',
                'error': 'not_found'
            }), 404
            
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'删除失败: {str(e)}',
            'error': 'delete_error'
        }), 500

@app.route('/outbound', methods=['GET', 'POST'])
@login_required
def outbound():
    if request.method == 'POST':
        try:
            sn_list = request.form.get('sn_list', '').strip()
            if not sn_list:
                flash('请输入序列号', 'danger')
                return redirect(url_for('outbound'))
            
            sns = [sn.strip() for sn in sn_list.split(',')]
            conn = sqlite3.connect('equipment.db')
            c = conn.cursor()
            
            # 检查设备是否存在且未出库
            c.execute('''SELECT sn FROM equipment 
                        WHERE sn IN ({}) AND status = '在库'
                        '''.format(','.join(['?'] * len(sns))), sns)
            valid_sns = [row[0] for row in c.fetchall()]
            
            if not valid_sns:
                flash('没有找到可出库的设备', 'danger')
                return redirect(url_for('outbound'))
            
            # 更新设备状态
            c.execute('''UPDATE equipment 
                        SET status = '出库'
                        WHERE sn IN ({})'''.format(','.join(['?'] * len(valid_sns))), valid_sns)
            conn.commit()
            
            # 记录操作日志
            log_operation('outbound', f'出库设备: {", ".join(valid_sns)}')
            
            flash(f'成功出库 {len(valid_sns)} 台设备', 'success')
            return redirect(url_for('outbound'))
        except Exception as e:
            logger.error(f"设备出库失败: {str(e)}")
            flash('设备出库失败，请稍后重试', 'danger')
        finally:
            conn.close()
    
    return render_template('outbound.html')

@app.route('/search_equipment', methods=['POST'])
def search_equipment():
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        data = request.get_json()
        sn_list = data.get('sn_list', [])
        
        if not sn_list:
            return jsonify({
                'success': False,
                'message': 'SN码列表不能为空',
                'error': 'empty_sn_list'
            }), 400
            
        logger.info(f'开始查询设备，SN列表: {sn_list}')
            
        # 查询设备信息
        equipment_list = []
        not_found_sn = []
        
        for sn in sn_list:
            result = DBOperations().get_equipment(sn)
            logger.info(f'查询结果: {result}')
            if result:
                equipment = result[0]  # 获取第一个匹配的设备
                logger.info(f'设备信息: {equipment}')
                # 只显示状态为"在库"的设备
                if equipment[7] == '在库':  # status
                    equipment_list.append({
                        'date': equipment[1],  # date
                        'sn': equipment[2],  # sn
                        'model': equipment[3],  # model
                        'manufacturer': equipment[4],  # manufacturer
                        'network_element': equipment[5],  # network_element
                        'type': equipment[6],  # type
                        'status': equipment[7],  # status
                        'standard': equipment[8],  # standard
                        'photo_path': equipment[9],  # photo_path
                        'outbound_time': equipment[10] if len(equipment) > 10 else None,  # outbound_time
                        'discard_time': equipment[11] if len(equipment) > 11 else None,  # discard_time
                        'original_network_element': equipment[12] if len(equipment) > 12 else None,  # original_network_element
                        'current_network_element': equipment[13] if len(equipment) > 13 else None  # current_network_element
                    })
                else:
                    not_found_sn.append(f"{sn}（状态不是在库）")
            else:
                not_found_sn.append(sn)
        
        response = {
            'success': True,
            'equipment': equipment_list
        }
        
        if not_found_sn:
            response['warning'] = f'以下SN码未找到或状态不符合要求: {", ".join(not_found_sn)}'
        
        logger.info(f'查询结果: {response}')
        return jsonify(response)
        
    except Exception as e:
        logger.error(f'查询失败: {str(e)}')
        logger.error(f'完整错误信息: {traceback.format_exc()}')
        return jsonify({
            'success': False,
            'message': f'查询失败: {str(e)}',
            'error': 'search_error'
        }), 500

@app.route('/confirm_outbound', methods=['POST'])
def confirm_outbound():
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        data = request.get_json()
        sn_list = data.get('sn_list', [])
        
        if not sn_list:
            return jsonify({
                'success': False,
                'message': '请选择要出库的设备',
                'error': 'empty_sn_list'
            }), 400
            
        # 执行出库操作
        success_count = 0
        failed_sn = []
        
        for sn in sn_list:
            if DBOperations().update_equipment_status(sn, '出库'):
                success_count += 1
                # 记录操作日志
                log_operation('outbound', f'设备出库: SN={sn}')
            else:
                failed_sn.append(sn)
        
        if success_count == len(sn_list):
            return jsonify({
                'success': True,
                'message': f'成功出库 {success_count} 个设备'
            })
        else:
            return jsonify({
                'success': False,
                'message': f'成功出库 {success_count} 个设备，失败 {len(failed_sn)} 个设备: {", ".join(failed_sn)}',
                'error': 'partial_failure'
            })
            
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'出库失败: {str(e)}',
            'error': 'outbound_error'
        }), 500

@app.route('/discard')
@login_required
def discard():
    """设备销库页面"""
    return render_template('discard.html')

@app.route('/search_discard_equipment', methods=['POST'])
@login_required
def search_discard_equipment():
    """查询待销库设备"""
    try:
        data = request.get_json()
        sn_list = data.get('sn_list', [])
        
        if not sn_list:
            return jsonify({
                'success': False,
                'message': '请提供SN码列表'
            })
        
        # 查询设备信息
        equipment_list = []
        for sn in sn_list:
            equipment = DBOperations().get_equipment(sn)
            if equipment:
                equipment_list.append({
                    'date': equipment[0][1],
                    'sn': equipment[0][2],
                    'model': equipment[0][3],
                    'manufacturer': equipment[0][4],
                    'network_element': equipment[0][5],
                    'type': equipment[0][6],
                    'standard': equipment[0][7],
                    'status': equipment[0][8]
                })
        
        return jsonify({
            'success': True,
            'equipment': equipment_list
        })
        
    except Exception as e:
        print(f"Error in search_discard_equipment: {str(e)}")
        return jsonify({
            'success': False,
            'message': '查询失败，请稍后重试'
        })

@app.route('/confirm_discard', methods=['POST'])
@login_required
def confirm_discard():
    """确认销库"""
    try:
        data = request.get_json()
        equipment_list = data.get('equipment_list', [])
        
        if not equipment_list:
            return jsonify({
                'success': False,
                'message': '请选择要销库的设备'
            })
        
        # 批量销库
        success_count = 0
        failed_sn = []
        
        for equipment in equipment_list:
            sn = equipment['sn']
            current_network_element = equipment.get('new_network_element')
            
            if DBOperations().discard_equipment(sn, current_network_element):
                success_count += 1
                # 记录操作日志
                log_operation('discard', f'设备销库: SN={sn}, 当前网元={current_network_element}')
            else:
                failed_sn.append(sn)
        
        if success_count == len(equipment_list):
            return jsonify({
                'success': True,
                'message': f'成功销库 {success_count} 台设备'
            })
        else:
            return jsonify({
                'success': False,
                'message': f'成功销库 {success_count} 台设备，失败 {len(failed_sn)} 台设备: {", ".join(failed_sn)}'
            })
            
    except Exception as e:
        logger.error(f"销库失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': '销库失败，请稍后重试'
        })

@app.route('/outbound_equipment', methods=['POST'])
def outbound_equipment():
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        data = request.get_json()
        sn_list = data.get('sn_list', [])
        
        if not sn_list:
            return jsonify({
                'success': False,
                'message': 'SN码列表不能为空',
                'error': 'empty_sn_list'
            }), 400
            
        success_count = 0
        failed_sn = []
        
        for sn in sn_list:
            if DBOperations().outbound_equipment(sn):
                success_count += 1
            else:
                failed_sn.append(sn)
                
        if success_count > 0:
            log_operation('outbound', f'出库设备 {success_count} 台')
            
        response = {
            'success': True,
            'message': f'成功出库 {success_count} 台设备'
        }
        
        if failed_sn:
            response['warning'] = f'以下SN码出库失败: {", ".join(failed_sn)}'
            
        return jsonify(response)
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'出库失败: {str(e)}',
            'error': 'outbound_error'
        }), 500

@app.route('/batch_discard', methods=['POST'])
def batch_discard():
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        # 检查是否有文件上传
        if 'file' not in request.files:
            return jsonify({
                'success': False,
                'message': '请上传CSV文件',
                'error': 'no_file'
            }), 400
            
        file = request.files['file']
        if file.filename == '':
            return jsonify({
                'success': False,
                'message': '未选择文件',
                'error': 'no_file'
            }), 400
            
        if not file.filename.endswith('.csv'):
            return jsonify({
                'success': False,
                'message': '请上传CSV格式的文件',
                'error': 'invalid_format'
            }), 400
            
        # 保存上传的文件
        temp_path = os.path.join('static', 'uploads', 'temp.csv')
        file.save(temp_path)
        
        # 读取CSV文件
        df = pd.read_csv(temp_path)
        
        # 检查必要字段
        required_fields = ['资产序列号', '网元名称']
        missing_fields = [field for field in required_fields if field not in df.columns]
        
        if missing_fields:
            return jsonify({
                'success': False,
                'message': f'CSV文件缺少必要字段: {", ".join(missing_fields)}',
                'error': 'missing_fields'
            }), 400

        # 连接数据库
        conn = sqlite3.connect('equipment.db')
        
        # 获取所有出库设备
        query = """
            SELECT date, sn, model, manufacturer, network_element, type, standard, status,
                   original_network_element, current_network_element, outbound_time
            FROM equipment
            WHERE status = '出库'
        """
        db_df = pd.read_sql_query(query, conn)
        
        # 将CSV数据与数据库数据进行左连接
        merged_df = pd.merge(
            db_df,
            df[['资产序列号', '网元名称']],
            left_on='sn',
            right_on='资产序列号',
            how='left'
        )
        
        # 提取匹配的设备信息
        equipment_list = []
        for _, row in merged_df.iterrows():
            if pd.notna(row['网元名称']):  # 只处理在CSV中找到匹配的设备
                equipment_list.append({
                    'date': row['date'],
                    'sn': row['sn'],
                    'model': row['model'],
                    'manufacturer': row['manufacturer'],
                    'original_network_element': row['original_network_element'],
                    'current_network_element': row['current_network_element'],
                    'new_network_element': row['网元名称'],
                    'type': row['type'],
                    'standard': row['standard'],
                    'status': row['status'],
                    'outbound_time': row['outbound_time']
                })
        
        # 清理临时文件
        if os.path.exists(temp_path):
            os.unlink(temp_path)
            
        if not equipment_list:
            return jsonify({
                'success': False,
                'message': '未找到匹配的出库设备',
                'error': 'no_matching_equipment'
            }), 400
            
        return jsonify({
            'success': True,
            'equipment': equipment_list
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'处理失败: {str(e)}',
            'error': 'process_error'
        }), 500

@app.route('/equipment_detail')
def equipment_detail():
    if 'username' not in session:
        return redirect(url_for('login'))
    
    sn = request.args.get('sn')
    if not sn:
        flash('请输入SN码', 'danger')
        return redirect(url_for('dashboard'))
    
    logger.info(f'用户 {session.get("username")} 正在查询设备，SN码: {sn}')
    
    try:
        conn = sqlite3.connect('equipment.db')
        c = conn.cursor()
        c.execute('SELECT * FROM equipment WHERE sn = ?', (sn,))
        equipment = c.fetchone()
        
        if equipment:
            equipment_dict = {
                'date': equipment[1],
                'sn': equipment[2],
                'model': equipment[3],
                'manufacturer': equipment[4],
                'network_element': equipment[5],
                'type': equipment[6],
                'status': equipment[7],
                'standard': equipment[8],
                'photo_path': equipment[9]
            }
            logger.info(f'设备查询成功 - SN: {sn}')
            log_operation('query', f'查询设备 - SN: {sn}')
            return render_template('equipment_detail.html', equipment=equipment_dict)
        else:
            logger.warning(f'设备查询失败: 未找到SN码 {sn}')
            flash('未找到该设备', 'danger')
            return redirect(url_for('dashboard'))
    except Exception as e:
        logger.error(f'设备查询失败: {str(e)}')
        logger.error(f'完整错误信息: {traceback.format_exc()}')
        flash('查询失败，请稍后重试', 'danger')
        return redirect(url_for('dashboard'))
    finally:
        conn.close()

@app.route('/inventory')
@login_required
def inventory():
    try:
        # 获取所有唯一值用于下拉列表
        manufacturers = DBOperations().get_unique_manufacturers()
        types = DBOperations().get_unique_types()
        standards = DBOperations().get_unique_standards()
        
        # 获取所有设备信息
        equipment_list = DBOperations().get_inventory()
        
        return render_template('inventory.html',
                             manufacturers=manufacturers,
                             types=types,
                             standards=standards,
                             equipment_list=equipment_list)
    except Exception as e:
        logger.error(f"获取库存数据失败: {str(e)}")
        flash('获取库存数据失败，请稍后重试', 'danger')
        return redirect(url_for('index'))

@app.route('/delete_inventory', methods=['POST'])
def delete_inventory():
    if 'username' not in session:
        return jsonify({'success': False, 'error': '未登录'}), 401
        
    data = request.get_json()
    if not data or 'sn_list' not in data:
        return jsonify({'success': False, 'error': '无效请求'}), 400
        
    try:
        db = DBOperations()
        count = db.batch_delete_equipment(data['sn_list'])
        return jsonify({'success': True, 'count': count})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/search_inventory')
def search_inventory():
    try:
        manufacturer = request.args.get('manufacturer', '')
        type = request.args.get('type', '')
        standard = request.args.get('standard', '')
        model = request.args.get('model', '')  # 添加设备型号查询条件
        
        # 搜索设备
        equipment = DBOperations().search_equipment(manufacturer, type, standard, model)
        
        return jsonify({
            'success': True,
            'equipment': equipment
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        })

@app.route('/export_inventory')
@login_required
def export_inventory():
    try:
        conn = sqlite3.connect('equipment.db')
        df = pd.read_sql_query('''SELECT date, sn, model, manufacturer, 
                                        network_element, type, standard, status
                                 FROM equipment''', conn)
        
        # 生成CSV文件
        filename = f'inventory_{datetime.datetime.now().strftime("%Y%m%d_%H%M%S")}.csv'
        filepath = os.path.join('static', 'uploads', filename)
        df.to_csv(filepath, index=False, encoding='utf-8-sig')
        
        # 记录操作日志
        log_operation('export', '导出库存数据')
        
        return send_file(filepath,
                        mimetype='text/csv',
                        as_attachment=True,
                        download_name=filename)
    except Exception as e:
        logger.error(f"导出库存数据失败: {str(e)}")
        flash('导出库存数据失败，请稍后重试', 'danger')
        return redirect(url_for('inventory'))
    finally:
        conn.close()

@app.route('/statistics')
@login_required
def statistics():
    """统计报表页面"""
    try:
        conn = sqlite3.connect('equipment.db')
        c = conn.cursor()
        
        # 获取所有制造商
        c.execute("SELECT DISTINCT manufacturer FROM equipment WHERE manufacturer IS NOT NULL")
        manufacturers = [row[0] for row in c.fetchall()]
        
        # 获取所有类型
        c.execute("SELECT DISTINCT type FROM equipment WHERE type IS NOT NULL")
        types = [row[0] for row in c.fetchall()]
        
        # 获取所有制式
        c.execute("SELECT DISTINCT standard FROM equipment WHERE standard IS NOT NULL")
        standards = [row[0] for row in c.fetchall()]
        
        # 获取所有设备型号
        c.execute("SELECT DISTINCT model FROM equipment WHERE model IS NOT NULL")
        models = [row[0] for row in c.fetchall()]
        
        return render_template('statistics.html',
                             manufacturers=manufacturers,
                             types=types,
                             standards=standards,
                             models=models)
    except Exception as e:
        logger.error(f"获取统计数据失败: {str(e)}")
        flash('获取统计数据失败，请稍后重试', 'danger')
        return render_template('statistics.html')
    finally:
        conn.close()

def get_db():
    """获取数据库连接"""
    conn = sqlite3.connect('equipment.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/get_statistics', methods=['POST'])
def get_statistics():
    try:
        # 获取查询参数
        group_by = request.form.get('group_by', 'type')
        time_dimension = request.form.get('time_dimension', 'day')
        start_date = request.form.get('start_date')
        end_date = request.form.get('end_date')
        status = request.form.getlist('status')  # 获取多个状态值
        
        # 记录查询参数
        app.logger.info(f"统计参数: group_by={group_by}, time_dimension={time_dimension}, start_date={start_date}, end_date={end_date}, status={status}")
        
        # 构建基础查询
        query = f"""
            SELECT
                {group_by} as group_name,
                strftime('%Y-%m-%d', date) as date_group,
                status,
                COUNT(*) as count
            FROM equipment
            WHERE 1=1
        """
        
        # 添加查询条件
        params = []
        if status:
            placeholders = ','.join(['?'] * len(status))
            query += f" AND status IN ({placeholders})"
            params.extend(status)
        if start_date:
            query += " AND date >= ?"
            params.append(start_date)
        if end_date:
            query += " AND date <= ?"
            params.append(end_date)
            
        # 添加分组和排序
        query += f"""
            GROUP BY {group_by}, strftime('%Y-%m-%d', date), status
            ORDER BY strftime('%Y-%m-%d', date), {group_by}
        """
        
        app.logger.info(f"SQL查询: {query}")
        app.logger.info(f"查询参数: {params}")
        
        # 执行查询
        conn = sqlite3.connect('equipment.db')
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(query, params)
        results = cursor.fetchall()
        conn.close()
        
        app.logger.info(f"查询结果: {results}")
        
        # 处理查询结果
        statistics = {}
        for row in results:
            date = row['date_group']
            group_name = row['group_name']
            status = row['status'].strip()  # 确保状态值没有空格
            count = row['count']
            
            if date not in statistics:
                statistics[date] = []
                
            # 查找是否已存在该分组
            group_data = next((g for g in statistics[date] if g['group_name'] == group_name), None)
            if not group_data:
                group_data = {
                    'group_name': group_name,
                    'inbound_count': 0,
                    'outbound_count': 0,
                    'discard_count': 0,
                    'total_count': 0
                }
                statistics[date].append(group_data)
            
            # 更新对应状态的计数
            if status == '在库':
                group_data['inbound_count'] = count
            elif status == '出库':
                group_data['outbound_count'] = count
            elif status == '销库':
                group_data['discard_count'] = count
            
            # 更新总数
            group_data['total_count'] = (
                group_data['inbound_count'] +
                group_data['outbound_count'] +
                group_data['discard_count']
            )
        
        app.logger.info(f"处理后的统计数据: {statistics}")
        
        # 返回JSON响应
        response = jsonify({
            'success': True,
            'statistics': statistics
        })
        response.headers['Content-Type'] = 'application/json'
        return response
        
    except Exception as e:
        app.logger.error(f"获取统计数据失败: {str(e)}")
        response = jsonify({
            'success': False,
            'message': f'获取统计数据失败: {str(e)}'
        })
        response.headers['Content-Type'] = 'application/json'
        return response

@app.route('/export_statistics')
@login_required
def export_statistics():
    """导出统计报表"""
    try:
        # 获取统计参数
        group_by = request.args.get('group_by', 'type')
        time_dimension = request.args.get('time_dimension', 'day')
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        status = request.args.getlist('status')  # 修改为获取多个状态值
        manufacturer = request.args.get('manufacturer')
        type = request.args.get('type')
        standard = request.args.get('standard')
        model = request.args.get('model')
        
        # 构建查询条件
        conditions = []
        params = []
        
        if start_date:
            conditions.append("date >= ?")
            params.append(start_date)
        if end_date:
            conditions.append("date <= ?")
            params.append(end_date)
        if status:
            placeholders = ','.join(['?'] * len(status))
            conditions.append(f"status IN ({placeholders})")
            params.extend(status)
        if manufacturer:
            conditions.append("manufacturer = ?")
            params.append(manufacturer)
        if type:
            conditions.append("type = ?")
            params.append(type)
        if standard:
            conditions.append("standard = ?")
            params.append(standard)
        if model:
            conditions.append("model = ?")
            params.append(model)
            
        # 构建查询语句
        where_clause = " AND ".join(conditions) if conditions else "1=1"
        
        # 根据时间维度构建日期分组
        date_group = {
            'day': "strftime('%Y-%m-%d', date)",
            'month': "strftime('%Y-%m', date)",
            'year': "strftime('%Y', date)",
            'total': "'总计'"
        }.get(time_dimension, "strftime('%Y-%m-%d', date)")
        
        query = f"""
            SELECT 
                {group_by} as group_name,
                {date_group} as date_group,
                SUM(CASE WHEN status = '在库' THEN 1 ELSE 0 END) as inbound_count,
                SUM(CASE WHEN status = '出库' THEN 1 ELSE 0 END) as outbound_count,
                SUM(CASE WHEN status = '销库' THEN 1 ELSE 0 END) as discard_count,
                COUNT(*) as total_count
            FROM equipment
            WHERE {where_clause}
            GROUP BY {group_by}, {date_group}
            ORDER BY {date_group}, {group_by}
        """
        
        # 获取统计数据
        conn = sqlite3.connect('equipment.db')
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(query, params)
        stats = cursor.fetchall()
        conn.close()
        
        # 生成CSV文件
        import csv
        import io
        
        output = io.StringIO()
        writer = csv.writer(output)
        
        # 写入表头
        writer.writerow(['时间', '分组', '在库数量', '出库数量', '销库数量', '总计'])
        
        # 写入数据
        for row in stats:
            writer.writerow([
                row['date_group'],
                row['group_name'],
                row['inbound_count'],
                row['outbound_count'],
                row['discard_count'],
                row['total_count']
            ])
            
        # 准备响应
        output.seek(0)
        return Response(
            output,
            mimetype="text/csv",
            headers={"Content-disposition": f"attachment; filename=statistics_{group_by}_{time_dimension}.csv"}
        )
        
    except Exception as e:
        logger.error(f"导出统计数据失败: {str(e)}")
        flash(f'导出失败: {str(e)}')
        return redirect(url_for('statistics'))

@app.route('/change_password', methods=['GET', 'POST'])
@login_required
def change_password():
    if request.method == 'POST':
        current_password = request.form.get('current_password')
        new_password = request.form.get('new_password')
        confirm_password = request.form.get('confirm_password')
        
        # 验证输入
        if not current_password or not new_password or not confirm_password:
            flash('请填写所有密码字段', 'danger')
            return redirect(url_for('change_password'))
        
        if new_password != confirm_password:
            flash('新密码和确认密码不匹配', 'danger')
            return redirect(url_for('change_password'))
        
        if len(new_password) < 6:
            flash('新密码长度至少为6个字符', 'danger')
            return redirect(url_for('change_password'))
        
        # 验证当前密码
        conn = sqlite3.connect('equipment.db')
        cursor = conn.cursor()
        cursor.execute('SELECT password FROM users WHERE username = ?', (session['username'],))
        user = cursor.fetchone()
        
        if not user or not check_password_hash(user['password'], current_password):
            flash('原密码错误', 'danger')
            return redirect(url_for('change_password'))
        
        # 更新密码
        hashed_password = generate_password_hash(new_password)
        cursor.execute('UPDATE users SET password = ? WHERE username = ?',
                      (hashed_password, session['username']))
        conn.commit()
        conn.close()
        
        flash('密码修改成功', 'success')
        return redirect(url_for('dashboard'))
    
    return render_template('change_password.html')

@app.route('/debug_equipment/<sn>')
def debug_equipment(sn):
    if 'username' not in session:
        return jsonify({'success': False, 'message': '请先登录'}), 401
        
    try:
        conn = sqlite3.connect('inventory.db')
        c = conn.cursor()
        c.execute('SELECT * FROM equipment WHERE sn = ?', (sn,))
        result = c.fetchall()
        conn.close()
        
        if result:
            equipment = result[0]
            return jsonify({
                'success': True,
                'equipment': {
                    'id': equipment[0],
                    'date': equipment[1],
                    'sn': equipment[2],
                    'model': equipment[3],
                    'manufacturer': equipment[4],
                    'network_element': equipment[5],
                    'type': equipment[6],
                    'status': equipment[7],
                    'standard': equipment[8],
                    'photo_path': equipment[9],
                    'outbound_time': equipment[10] if len(equipment) > 10 else None,
                    'discard_time': equipment[11] if len(equipment) > 11 else None,
                    'original_network_element': equipment[12] if len(equipment) > 12 else None,
                    'current_network_element': equipment[13] if len(equipment) > 13 else None
                }
            })
        else:
            return jsonify({
                'success': False,
                'message': f'未找到设备 {sn}'
            })
            
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'查询失败: {str(e)}'
        })

@app.route('/api/equipment', methods=['POST'])
def add_equipment():
    try:
        data = request.get_json()
        
        # 验证必填字段
        required_fields = ['name', 'model', 'sn_code', 'location', 'status']
        for field in required_fields:
            if not data.get(field):
                return jsonify({'error': f'缺少必填字段: {field}'}), 400

        # 验证SN码格式
        sn_code = data['sn_code']
        if not (re.match(r'^\d{13}$', sn_code) or re.match(r'^[A-Za-z0-9]{10,}$', sn_code)):
            return jsonify({'error': 'SN码格式不正确'}), 400

        # 检查SN码是否已存在
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT id FROM equipment WHERE sn_code = ?', (sn_code,))
        if cursor.fetchone():
            conn.close()
            return jsonify({'error': 'SN码已存在'}), 400

        # 插入新设备
        cursor.execute('''
            INSERT INTO equipment (name, model, sn_code, location, status, purchase_date, warranty_period, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            data['name'],
            data['model'],
            sn_code,
            data['location'],
            data['status'],
            data.get('purchase_date'),
            data.get('warranty_period'),
            data.get('notes', '')
        ))
        
        conn.commit()
        conn.close()
        
        return jsonify({'message': '设备添加成功'}), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000,debug=True)